﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class DSTDYARNData : BaseFunction
    {
        
        public DSTDYARNData()
            : base()
        {

        }
        public DSTDYARNData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public DataSet GetARTICLE(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ARTICLE";
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string COLOR = dwABPOS["COLOR"].ToString().Trim();
            string COLORE = dwABPOS["COLORE"].ToString().Trim();
            string COLOR1 = dwABPOS["COLOR1"].ToString().Trim();
            string COLOR2 = dwABPOS["COLOR2"].ToString().Trim();
            string COLOR3 = dwABPOS["COLOR3"].ToString().Trim();
            string COLOR4 = dwABPOS["COLOR4"].ToString().Trim();
            string COLOR5 = dwABPOS["COLOR5"].ToString().Trim();
            string COLOR6 = dwABPOS["COLOR6"].ToString().Trim();
            string COLOR7 = dwABPOS["COLOR7"].ToString().Trim();
            string COLOR8 = dwABPOS["COLOR8"].ToString().Trim();
            string COLOR9 = dwABPOS["COLOR9"].ToString().Trim();
            string COLOR10 = dwABPOS["COLOR10"].ToString().Trim();
            string COLOR11 = dwABPOS["COLOR11"].ToString().Trim();
            string COLOR12 = dwABPOS["COLOR12"].ToString().Trim();
            string COLOR13 = dwABPOS["COLOR13"].ToString().Trim();
            string COLOR14 = dwABPOS["COLOR14"].ToString().Trim();
            string COLOR15 = dwABPOS["COLOR15"].ToString().Trim();
            string COLOR16 = dwABPOS["COLOR16"].ToString().Trim();
            string COLOR17 = dwABPOS["COLOR17"].ToString().Trim();
            string COLOR18 = dwABPOS["COLOR18"].ToString().Trim();
            string COLOR19 = dwABPOS["COLOR19"].ToString().Trim();
            string COLOR20 = dwABPOS["COLOR20"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT ARTICLE,ART,FARBE,BEM,SUM(REQ) AS REQ,sum(qty) AS QTY FROM ( "
                    + " SELECT trim(B.WAFART) || '-' || trim(B.WAFZUSATZ) as ARTICLE, "
                    + " TRIM(A.WAGGART) AS ART,TRIM(A.WAGGFARBE) AS FARBE, "
                    + " TRIM(A.WAGBEM) AS BEM,WAFMUDESS AS DESS, "
                    + " Round(WAGKGBRUT * WAFMUDESS *GAGKGBRUT*0.0000001* 0.01 * 0.001, 1) AS REQ, "
                    + " Round(WAGKGBRUT * WAFMUDESS * 0.01 * 0.001, 1) AS qty "
                    + " FROM WASTGA A, WAFARBEN B,GASTGA C "
                    + "  WHERE A.WAGMANDANT = B.WAFMANDANT "
                    + " AND A.WAGART = B.WAFART AND A.WAGVAR = B.WAFVAR "
                    + " AND A.WAGWCOL = B.WAFZUSATZ AND A.WAGMANDANT = C.GAGMANDANT  "
                    + " AND A.WAGGART = C.GAGART AND A.WAGGVAR = C.GAGVAR "
                    + " AND B.WAFZUART = 'C' AND B.WAFMANDANT ='00' AND B.WAFKUNDNR = '00000000' "
                    + " AND B.WAFWERK= '   ' AND B.WAFABT = '   ' "
                    + " AND B.WAFART LIKE '" + ARTICLE + "%'  "
                    + " AND (LPAD(TRIM(B.WAFZUSATZ),2,'0') IN ('" + COLOR1 + "','" + COLOR2 + "','" + COLOR3 + "','" + COLOR4 + "','" + COLOR5 + "',  "
                    + " '" + COLOR6 + "','" + COLOR7 + "','" + COLOR8 + "','" + COLOR9 + "','" + COLOR10 + "','" + COLOR11 + "','" + COLOR12 + "','" + COLOR13 + "', "
                    + " '" + COLOR14 + "','" + COLOR15 + "','" + COLOR16 + "','" + COLOR17 + "','" + COLOR18 + "','" + COLOR19 + "','" + COLOR20 + "') "
                    + " OR (LPAD(TRIM(B.WAFZUSATZ),2,'0')>='" + COLOR + "' AND LPAD(TRIM(B.WAFZUSATZ),2,'0')<='" + COLORE + "')) "
                    + " union all "
                    + " select trim(C.WAFART) || '-' || TRIM(C.WAFZUSATZ) AS ARTICLE, "
                    + " TRIM(A.BAGGART) AS ART,TRIM(A.BAGGFARBE) AS FARBE, "
                    + " TRIM(A.BAGBEM) AS BEM,C.WAFGRPBEZ AS DESS, "
                    //+ " Round(BAGKGBRUT * WAFGRPBEZ* GAGKGBRUT*0.0000001 * 0.01 * 0.01 * 0.01, 1) AS REQ, "
                    + " to_number(NVL(TRIM(E.BAGKGNET),'0'))*0.001 AS REQ, "
                    + " round(TO_NUMBER(NVL(TRIM(E.BAGKGNET),'0'))*0.92*0.001,1) AS QTY "
                    //+ " Round(BAGKGBRUT * WAFGRPBEZ* 0.01 * 0.01 * 0.01, 1) AS qty "
                    + " from bastga A, WASTKE B, WAFARBEN C,GASTGA D,BASTGAD E "
                    + " where A.BAGMANDANT = B.WAKMANDANT AND A.BAGART = B.WAKKART AND A.BAGVAR = B.WAKKVAR "
                    + " AND A.BAGFARBE = B.WAKKFARBE AND B.WAKMANDANT = C.WAFMANDANT "
                    + " AND B.WAKART = C.WAFART AND B.WAKWCOL = C.WAFZUSATZ "
                    + " AND A.BAGMANDANT = D.GAGMANDANT AND A.BAGGART = D.GAGART AND A.BAGGVAR = D.GAGVAR "
                    + " AND A.BAGMANDANT = E.BAGMANDANT(+) AND A.BAGART = E.BAGART(+) "
                    + " AND A.BAGVAR = E.BAGVAR(+) AND A.BAGFARBE = E.BAGFARBE(+) "
                    + " AND A.BAGPOS = E.BAGPOS(+) "
                    + " AND C.WAFZUART = 'C' AND C.WAFMANDANT ='00' AND C.WAFKUNDNR = '00000000' "
                    + " AND C.WAFWERK= '   ' AND C.WAFABT = '   '  "
                    + " AND C.WAFART LIKE '" + ARTICLE + "%' "
                    + " AND (LPAD(TRIM(C.WAFZUSATZ),2,'0') IN ('" + COLOR1 + "','" + COLOR2 + "','" + COLOR3 + "','" + COLOR4 + "','" + COLOR5 + "',  "
                    + " '" + COLOR6 + "','" + COLOR7 + "','" + COLOR8 + "','" + COLOR9 + "','" + COLOR10 + "','" + COLOR11 + "','" + COLOR12 + "','" + COLOR13 + "', "
                    + " '" + COLOR14 + "','" + COLOR15 + "','" + COLOR16 + "','" + COLOR17 + "','" + COLOR18 + "','" + COLOR19 + "','" + COLOR20 + "') "
                    + " OR (LPAD(TRIM(C.WAFZUSATZ),2,'0')>='" + COLOR + "' AND LPAD(TRIM(C.WAFZUSATZ),2,'0')<='" + COLORE + "'))) "
                    + " GROUP BY ARTICLE,ART,FARBE,BEM  "
                    + " ORDER BY 1, 2,3 "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BEM"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BEM"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetDYARN(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "DYARN";
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string COLOR = dwABPOS["COLOR"].ToString().Trim();
            string COLORE = dwABPOS["COLORE"].ToString().Trim();
            string COLOR1 = dwABPOS["COLOR1"].ToString().Trim();
            string COLOR2 = dwABPOS["COLOR2"].ToString().Trim();
            string COLOR3 = dwABPOS["COLOR3"].ToString().Trim();
            string COLOR4 = dwABPOS["COLOR4"].ToString().Trim();
            string COLOR5 = dwABPOS["COLOR5"].ToString().Trim();
            string COLOR6 = dwABPOS["COLOR6"].ToString().Trim();
            string COLOR7 = dwABPOS["COLOR7"].ToString().Trim();
            string COLOR8 = dwABPOS["COLOR8"].ToString().Trim();
            string COLOR9 = dwABPOS["COLOR9"].ToString().Trim();
            string COLOR10 = dwABPOS["COLOR10"].ToString().Trim();
            string COLOR11 = dwABPOS["COLOR11"].ToString().Trim();
            string COLOR12 = dwABPOS["COLOR12"].ToString().Trim();
            string COLOR13 = dwABPOS["COLOR13"].ToString().Trim();
            string COLOR14 = dwABPOS["COLOR14"].ToString().Trim();
            string COLOR15 = dwABPOS["COLOR15"].ToString().Trim();
            string COLOR16 = dwABPOS["COLOR16"].ToString().Trim();
            string COLOR17 = dwABPOS["COLOR17"].ToString().Trim();
            string COLOR18 = dwABPOS["COLOR18"].ToString().Trim();
            string COLOR19 = dwABPOS["COLOR19"].ToString().Trim();
            string COLOR20 = dwABPOS["COLOR20"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT ART,FARBE,BEM,SUM(REQ) AS REQ,sum(qty) AS QTY FROM ( "
                    + " SELECT trim(B.WAFART) || '-' || trim(B.WAFZUSATZ) as ARTICLE, "
                    + " TRIM(A.WAGGART) AS ART,TRIM(A.WAGGFARBE) AS FARBE, "
                    + " TRIM(A.WAGBEM) AS BEM,WAFMUDESS AS DESS, "
                    + " Round(WAGKGBRUT * WAFMUDESS *GAGKGBRUT*0.0000001* 0.01 * 0.001, 1) AS REQ, "
                    + " Round(WAGKGBRUT * WAFMUDESS * 0.01 * 0.001, 1) AS qty "
                    + " FROM WASTGA A, WAFARBEN B,GASTGA C "
                    + "  WHERE A.WAGMANDANT = B.WAFMANDANT "
                    + " AND A.WAGART = B.WAFART AND A.WAGVAR = B.WAFVAR "
                    + " AND A.WAGWCOL = B.WAFZUSATZ AND A.WAGMANDANT = C.GAGMANDANT  "
                    + " AND A.WAGGART = C.GAGART AND A.WAGGVAR = C.GAGVAR "
                    + " AND B.WAFZUART = 'C' AND B.WAFMANDANT ='00' AND B.WAFKUNDNR = '00000000' "
                    + " AND B.WAFWERK= '   ' AND B.WAFABT = '   ' "
                    + " AND B.WAFART LIKE '"+ARTICLE+"%'  "
                    + " AND (LPAD(TRIM(B.WAFZUSATZ),2,'0') IN ('"+COLOR1+"','"+COLOR2+"','"+COLOR3+"','"+COLOR4+"','"+COLOR5+"',  "
                    + " '"+COLOR6+"','"+COLOR7+"','"+COLOR8+"','"+COLOR9+"','"+COLOR10+"','"+COLOR11+"','"+COLOR12+"','"+COLOR13+"', "
                    + " '"+COLOR14+"','"+COLOR15+"','"+COLOR16+"','"+COLOR17+"','"+COLOR18+"','"+COLOR19+"','"+COLOR20+"') "
                    + " OR (LPAD(TRIM(B.WAFZUSATZ),2,'0')>='"+COLOR+"' AND LPAD(TRIM(B.WAFZUSATZ),2,'0')<='"+COLORE+"')) "
                    + " union all "
                    + " select trim(C.WAFART) || '-' || TRIM(C.WAFZUSATZ) AS ARTICLE, "
                    + " TRIM(A.BAGGART) AS ART,TRIM(A.BAGGFARBE) AS FARBE, "
                    + " TRIM(A.BAGBEM) AS BEM,C.WAFGRPBEZ AS DESS, "
                    //+ " Round(BAGKGBRUT * WAFGRPBEZ* GAGKGBRUT*0.0000001 * 0.01 * 0.01 * 0.01, 1) AS REQ, "
                    + " to_number(NVL(TRIM(E.BAGKGNET),'0'))*0.001 AS REQ, "
                    + " round(TO_NUMBER(NVL(TRIM(E.BAGKGNET),'0'))*0.92*0.001,1) AS QTY "
                    //+ " Round(BAGKGBRUT * WAFGRPBEZ* 0.01 * 0.01 * 0.01, 1) AS qty "
                    + " from bastga A, WASTKE B, WAFARBEN C,GASTGA D,BASTGAD E "
                    + " where A.BAGMANDANT = B.WAKMANDANT AND A.BAGART = B.WAKKART AND A.BAGVAR = B.WAKKVAR "
                    + " AND A.BAGFARBE = B.WAKKFARBE AND B.WAKMANDANT = C.WAFMANDANT "
                    + " AND B.WAKART = C.WAFART AND B.WAKWCOL = C.WAFZUSATZ "
                    + " AND A.BAGMANDANT = D.GAGMANDANT AND A.BAGGART = D.GAGART AND A.BAGGVAR = D.GAGVAR "
                    + " AND A.BAGMANDANT = E.BAGMANDANT(+) AND A.BAGART = E.BAGART(+) "
                    + " AND A.BAGVAR = E.BAGVAR(+) AND A.BAGFARBE = E.BAGFARBE(+) "
                    + " AND A.BAGPOS = E.BAGPOS(+) "
                    + " AND C.WAFZUART = 'C' AND C.WAFMANDANT ='00' AND C.WAFKUNDNR = '00000000' "
                    + " AND C.WAFWERK= '   ' AND C.WAFABT = '   '  "
                    + " AND C.WAFART LIKE '"+ARTICLE+"%' "
                    + " AND (LPAD(TRIM(C.WAFZUSATZ),2,'0') IN ('"+COLOR1+"','"+COLOR2+"','"+COLOR3+"','"+COLOR4+"','"+COLOR5+"',  "
                    + " '"+COLOR6+"','"+COLOR7+"','"+COLOR8+"','"+COLOR9+"','"+COLOR10+"','"+COLOR11+"','"+COLOR12+"','"+COLOR13+"', "
                    + " '"+COLOR14+"','"+COLOR15+"','"+COLOR16+"','"+COLOR17+"','"+COLOR18+"','"+COLOR19+"','"+COLOR20+"') "
                    + " OR (LPAD(TRIM(C.WAFZUSATZ),2,'0')>='"+COLOR+"' AND LPAD(TRIM(C.WAFZUSATZ),2,'0')<='"+COLORE+"'))) "
                    + " GROUP BY ART,FARBE,BEM  "
                    + " ORDER BY 1, 2 "
                    ;                    
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {      
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BEM"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BEM"] = value;
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet BuildDSTDYARNData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetARTICLE(dwUI);
                setReturn.Merge(setResult); 
                setResult = GetDYARN(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        
    }
}